/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yy.bugzillahelp.action.product;

import com.yy.bugzillahelp.action.BugAction;
import com.yy.bugzillahelp.data.ProductData;
import com.yy.bugzillahelp.tool.SQLTool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author hualun-alan
 */
public class ProductAction {

    private static final Logger logger = Logger.getLogger(BugAction.class.getName());

    public static int insertOne(Connection con, ProductData productData, String serverId) {
        String sql1 = "insert into products(product_id,name,desc,is_active,allwos_unconfirmed,server_id)"
                + " values (?,?,?,?,?,?)";

        PreparedStatement ps1 = null;
        try {
            con.setAutoCommit(false);

            ps1 = con.prepareStatement(sql1);
            ps1.setString(1, productData.getId());
            ps1.setString(2, productData.getName());
            ps1.setString(3, productData.getDesc());
            ps1.setString(4, productData.isActive() ? "yes" : "no");
            ps1.setString(5, productData.isAllowsUnconfirmed() ? "yes" : "no");
            ps1.setString(6, serverId);
            ps1.executeUpdate();

            VersionAction.insertList(con, productData.getVersions(), serverId);
            ComponentAction.insertList(con, productData.getComponents(), serverId);

            con.commit();
            return 1;
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
        }
    }

    public static int insertList(Connection con, ArrayList<ProductData> productDatas, String serverId) {
        String sql1 = "insert into products(product_id,name,desc,is_active,allwos_unconfirmed,server_id)"
                + " values (?,?,?,?,?,?)";

        PreparedStatement ps1 = null;
        try {
            con.setAutoCommit(false);

            ps1 = con.prepareStatement(sql1);

            for (ProductData productData : productDatas) {
                ps1.setString(1, productData.getId());
                ps1.setString(2, productData.getName());
                ps1.setString(3, productData.getDesc());
                ps1.setString(4, productData.isActive() ? "yes" : "no");
                ps1.setString(5, productData.isAllowsUnconfirmed() ? "yes" : "no");
                ps1.setString(6, serverId);
                ps1.executeUpdate();

                VersionAction.insertList(con, productData.getVersions(), serverId);
                ComponentAction.insertList(con, productData.getComponents(), serverId);
            }

            con.commit();
            return 1;
        } catch (SQLException ex) {
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, "sql1 = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
        }
    }

    public static int updateOne(Connection con, ProductData newData, String serverId) {
        ProductData oldData = searchById(con, newData.getId(), serverId);

        if (oldData.getId() == null || oldData.getId().trim().isEmpty()) {
            return insertOne(con, newData, serverId);
        } else {
            String sql1 = "update products set name=?,desc=?,is_active=?,allwos_unconfirmed=?"
                    + " where product_id=? and server_id=?";

            PreparedStatement ps1 = null;
            try {
                ps1 = con.prepareStatement(sql1);

                ps1.setString(1, newData.getName());
                ps1.setString(2, newData.getDesc());
                ps1.setString(3, newData.isActive() ? "yes" : "no");
                ps1.setString(4, newData.isAllowsUnconfirmed() ? "yes" : "no");
                ps1.setString(5, newData.getId());
                ps1.setString(6, serverId);
                ps1.executeUpdate();

                VersionAction.delete(con, newData.getId(), serverId);
                VersionAction.insertList(con, newData.getVersions(), serverId);

                ComponentAction.delete(con, newData.getId(), serverId);
                ComponentAction.insertList(con, newData.getComponents(), serverId);

                return 1;
            } catch (SQLException ex) {
                logger.log(Level.INFO, "sql = {0}", sql1);
                logger.log(Level.SEVERE, null, ex);
                return -1;
            } finally {
                SQLTool.closeStatement(ps1);
            }
        }
    }

    public static int updateList(Connection con, ArrayList<ProductData> productDatas, String serverId) {
        String sql1 = "update products set name=?,desc=?,is_active=?,allwos_unconfirmed=?"
                + " where product_id=? and server_id=?";

        PreparedStatement ps1 = null;

        try {
            ps1 = con.prepareStatement(sql1);

            for (ProductData newData : productDatas) {
                ProductData oldData = searchById(con, newData.getId(), serverId);

                if (oldData.getId() == null || oldData.getId().trim().isEmpty()) {
                    insertOne(con, newData, serverId);
                } else {
                    ps1.setString(1, newData.getName());
                    ps1.setString(2, newData.getDesc());
                    ps1.setString(3, newData.isActive() ? "yes" : "no");
                    ps1.setString(4, newData.isAllowsUnconfirmed() ? "yes" : "no");
                    ps1.setString(5, newData.getId());
                    ps1.setString(6, serverId);
                    ps1.executeUpdate();

                    VersionAction.delete(con, newData.getId(), serverId);
                    VersionAction.insertList(con, newData.getVersions(), serverId);

                    ComponentAction.delete(con, newData.getId(), serverId);
                    ComponentAction.insertList(con, newData.getComponents(), serverId);
                }
            }
            return 1;
        } catch (SQLException ex) {
            logger.log(Level.INFO, "sql = {0}", sql1);
            Logger.getLogger(BugAction.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            SQLTool.closeStatement(ps1);
        }
    }

    /**
     *
     * @param con
     * @param productId
     * @return ProductData, null if occurs exception
     */
    public static ProductData searchById(Connection con, String productId, String serverId) {
        ProductData productData = new ProductData();

        String sql1 = "select * from products where product_id = ? and server_id = ?";
        PreparedStatement ps1 = null;
        try {
            ps1 = con.prepareStatement(sql1);
            ps1.setString(1, productId);
            ps1.setString(2, serverId);
            ResultSet rs1 = ps1.executeQuery();
            if (rs1.next()) {
                getDetailData(productData, rs1);
                productData.setComponents(ComponentAction.searchComponents(con, productId, serverId));
                productData.setVersions(VersionAction.searchVersions(con, productId, serverId));
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "sql1 = {0}", sql1);
            logger.log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps1);
        }
        return productData;
    }

    /**
     *
     * @param con
     * @param id
     * @return a list of ProductData, null if occurs exception
     */
    public static ArrayList<ProductData> searchAll(Connection con, String serverId) {
        ArrayList<ProductData> list = new ArrayList<ProductData>();

        String sql = "select * from bug_detail where server_id = ? order by product_id";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, serverId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ProductData data = new ProductData();
                getDetailData(data, rs);
                data.setComponents(ComponentAction.searchComponents(con, data.getId(), serverId));
                data.setVersions(VersionAction.searchVersions(con, data.getId(), serverId));
                list.add(data);
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "sql = {0}", sql);
            logger.log(Level.SEVERE, null, ex);
            return null;
        } finally {
            SQLTool.closeStatement(ps);
        }

        return list;
    }

    private static void getDetailData(ProductData data, ResultSet rs) throws SQLException {
        data.setId(rs.getString("product_id"));
        data.setName(rs.getString("name"));
        data.setDesc(rs.getString("desc"));
        data.setActive("yes".equalsIgnoreCase(rs.getString("is_active")));
        data.setAllowsUnconfirmed("yes".equalsIgnoreCase(rs.getString("allwos_unconfirmed")));
    }

    public static int delete(Connection con, String serverId) {
        return SQLTool.executeUpdate(con, "delete from products where server_id = '" + serverId + "'");
    }
}
